1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmStockRecord
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("SELECT ST_ID, RTRIM(Stock_ID), [Date], Supplier.ID,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock where Supplier.ID=Stock.SupplierID order by [Date]", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24 End Sub
25
26 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
27 Try
28 If dgw.Rows.Count > 0 Then
29 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
30 frmStock.Show()
31 Me.Hide()
32 frmStock.txtST_ID.Text = dr.Cells(0).Value.ToString()
33 frmStock.txtStockID.Text = dr.Cells(1).Value.ToString()
34 frmStock.dtpDate.Text = dr.Cells(2).Value.ToString()
35 frmStock.txtSup_ID.Text = dr.Cells(3).Value.ToString()
36 frmStock.txtSupplierID.Text = dr.Cells(4).Value.ToString()
37 frmStock.txtSupplierName.Text = dr.Cells(5).Value.ToString()
38 frmStock.txtGrandTotal.Text = dr.Cells(6).Value.ToString()
39 frmStock.txtTotalPayment.Text = dr.Cells(7).Value.ToString()
40 frmStock.txtPaymentDue.Text = dr.Cells(8).Value.ToString()
41 frmStock.txtRemarks.Text = dr.Cells(9).Value.ToString()
42 frmStock.btnSave.Enabled = False
43 frmStock.btnUpdate.Enabled = True
44 frmStock.dtpDate.Enabled = False
45 frmStock.DataGridView1.Enabled = False
46 frmStock.btnAdd.Enabled = False
47 con = New SqlConnection(cs)
48 con.Open()
49 Dim sql As String = "SELECT PID,RTRIM(Product.ProductCode),RTRIM(Productname),Qty,Price,TotalAmount from Stock,Stock_Product,product where product.PID=Stock_product.ProductID and Stock.ST_ID=Stock_Product.StockID and ST_ID=" & dr.Cells(0).Value & ""
50 cmd = New SqlCommand(sql, con)
51 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
52 frmStock.DataGridView1.Rows.Clear()
53 While (rdr.Read() = True)
54 frmStock.DataGridView1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5))
55 End While
56 con.Close()
57 End If
58 Catch ex As Exception
59 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
60 End Try
61 End Sub
62
63 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
64 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
65 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
66 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
67 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
68 End If
69 Dim b As Brush = SystemBrushes.ControlText
70 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
71
72 End Sub
73 Sub Reset()
74 txtSupplierName.Text = ""
75 dtpDateFrom.Text = Today
76 dtpDateTo.Text = Today
77 DateTimePicker2.Text = Today
78 DateTimePicker1.Text = Today
79 Getdata()
80 End Sub
81 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
82 Reset()
83 End Sub
84
85 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
86 Me.Close()
87 End Sub
88
89 Private Sub txtSupplierName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSupplierName.TextChanged
90 Try
91 con = New SqlConnection(cs)
92 con.Open()
93 cmd = New SqlCommand("SELECT ST_ID, RTRIM(Stock_ID), [Date], Supplier.ID,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock where Supplier.ID=Stock.SupplierID and [Name] like '%" & txtSupplierName.Text & "%' order by [Date]", con)
94 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
95 dgw.Rows.Clear()
96 While (rdr.Read() = True)
97 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
98 End While
99 con.Close()
100 Catch ex As Exception
101 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
102 End Try
103 End Sub
104
105
106 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
107 Dim rowsTotal, colsTotal As Short
108 Dim I, j, iC As Short
109 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
110 Dim xlApp As New Excel.Application
111 Try
112 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
113 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
114 xlApp.Visible = True
115
116 rowsTotal = dgw.RowCount
117 colsTotal = dgw.Columns.Count - 1
118 With excelWorksheet
119 .Cells.Select()
120 .Cells.Delete()
121 For iC = 0 To colsTotal
122 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
123 Next
124 For I = 0 To rowsTotal - 1
125 For j = 0 To colsTotal
126 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
127 Next j
128 Next I
129 .Rows("1:1").Font.FontStyle = "Bold"
130 .Rows("1:1").Font.Size = 12
131
132 .Cells.Columns.AutoFit()
133 .Cells.Select()
134 .Cells.EntireColumn.AutoFit()
135 .Cells(1, 1).Select()
136 End With
137 Catch ex As Exception
138 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
139 Finally
140 'RELEASE ALLOACTED RESOURCES
141 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
142 xlApp = Nothing
143 End Try
144 End Sub
145
146 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
147 Try
148 con = New SqlConnection(cs)
149 con.Open()
150 cmd = New SqlCommand("SELECT ST_ID, RTRIM(Stock_ID), [Date], Supplier.ID,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock where Supplier.ID=Stock.SupplierID and [Date] between @d1 and @d2 order by [Date]", con)
151 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
152 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
153 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
154 dgw.Rows.Clear()
155 While (rdr.Read() = True)
156 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
157 End While
158 con.Close()
159 Catch ex As Exception
160 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
161 End Try
162 End Sub
163
164 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
165 Try
166 con = New SqlConnection(cs)
167 con.Open()
168 cmd = New SqlCommand("SELECT ST_ID, RTRIM(Stock_ID), [Date], Supplier.ID,RTRIM(Supplier.SupplierID),RTRIM(Supplier.Name), GrandTotal, TotalPayment, PaymentDue, RTRIM(Stock.Remarks) from Supplier,Stock where Supplier.ID=Stock.SupplierID and [Date] between @d1 and @d2 and PaymentDue > 0 order by [Date]", con)
169 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker2.Value.Date
170 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = DateTimePicker1.Value
171 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
172 dgw.Rows.Clear()
173 While (rdr.Read() = True)
174 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9))
175 End While
176 con.Close()
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181 End Class
